<?php
/* ******************************
 *	letterA aJax actions
 * ******************************
 */

include('../../includes/classes/core.php');
$action		= $_REQUEST['act'];
$error		= '';
$data		= '';
$user_id	= $_SESSION['USERID'];
$sum        = '';

switch ($action) {
    case 'get_edit_page':
	    $letter_id	= $_REQUEST['id'];
		$page		= GetPage( GetAletterInfo($letter_id) );

        $data 		= array('page'	=> $page);

        break;
    case 'add_toner':
        mysql_query("INSERT INTO `warehouse_producing` (`tester_id`, `person_id`, `warehouse_detail_id`, `cartridge_part_id`, `production_id`)
                        VALUES ('7', '42', '$_REQUEST[wd_id]', '2', '305')");
        break;

    case 'get_list':
	    $count = $_REQUEST['count'];
	    $hidden = $_REQUEST['hidden'];

	    $rResult = mysql_query("SELECT 	`a_letter`.`id`,
                            			`a_letter`.fact_end_date,
                            			 CONCAT( '№: ბ-',`a_letter`.`id`),
                            			 CONCAT(client.`name`, '(', client_objects.`name`, ')'),
                            			 sum(IF(warehouse_detail.type=2 AND b_letter_details.part_type_id=2,0.00,b_letter_details.price)),
                            			 CASE	WHEN b_letter_status != 0 THEN	'კი'
                            				    WHEN b_letter_status  = 0 THEN	'არა'
                            			 END AS `signature`,
	                                     invoice_id  
                                FROM    a_letter
                                JOIN    b_letter_details 	ON 	b_letter_details.a_letter_id=a_letter.id
                                JOIN    warehouse_detail 	ON 	warehouse_detail.id=b_letter_details.warehouse_detail_id and warehouse_detail.actived = 1
                                JOIN    client_objects 		ON 	client_objects.id = a_letter.client_object_id
                                JOIN    client 				ON 	client.id = client_objects.client_id
                                WHERE   a_letter.`status` >= 4 and a_letter.actived = 1 $_REQUEST[filter]
                                GROUP BY 	a_letter.id");

		$data = array(
			"aaData"	=> array()
		);

		while ( $aRow = mysql_fetch_array( $rResult ) )
		{
			$row = array();
			for ( $i = 0 ; $i < $count ; $i++ )
			{
				/* General output */
				$row[] = $aRow[$i];
			}
			$data['aaData'][] = $row;
		}

        break;
    case 'save_bletter_status' :
    	$altter_id     = $_REQUEST['id'];
    	$requester     = $_REQUEST['req'];
    	$new_requester = $_REQUEST['nreq'];
    	$client_obj    = $_REQUEST['cobj'];
    	$responsible_person = $_REQUEST['rp'];
    	$car_id 			= $_REQUEST['ci'];
    	Savebletter($user_id, $altter_id, $requester, $new_requester, $client_obj,$responsible_person,$car_id);

    	break;
	case 'get_overhead_id':
		$letter_id		= $_REQUEST['id'];
	   if(mysql_num_rows(mysql_query("SELECT b_letter_signature FROM `a_letter` WHERE id=$letter_id AND NOT ISNULL(b_letter_signature)"))>0) {
		  $overhead_id	= GetOverheadID($letter_id);
		  ChangeLocation($letter_id);
		  $data = array('overhead_id' => $overhead_id);
		}
		else{   $error = 'შემოიტანეთ ხელმომწერი';	}		
		
////////////////////////////--------------		
		

		break;
    case 'make_sale':
    	$altter_id     = $_REQUEST['id'];
    	MakeSale($user_id, $altter_id);

    	break;
    default:
       $error = 'Action is Null';
}

$data['error'] = $error;
echo json_encode($data);

function GetAletterInfo($letter_id){
	$res = mysql_fetch_assoc(mysql_query("SELECT	`a_letter`.`id` as `id`,
													`a_letter`.`start_date` as `date`,
	                                                 transporter_company_id,   	 
													CONCAT(`client`.`name`,'(',`client_objects`.`name`, ')') AS `client`,
													`a_letter`.`comment` as `coment`
									FROM			`a_letter`
									LEFT JOIN		`client_objects` ON `a_letter`.`client_object_id` = `client_objects`.`id`
									LEFT JOIN		`client` ON `client`.`id` = `client_objects`.`client_id`
									WHERE			`a_letter`.`id` = '$letter_id'"));
	return $res;
}

function GetOverheadID($letter_id){
	$res = mysql_fetch_assoc(mysql_query("	SELECT     `b_letter_overhead_id`
											FROM       `a_letter`
											WHERE      `id` = '$letter_id'"));
	return $res['b_letter_overhead_id'];
}
function ChangeLocation($letter_id){
	$csql = mysql_query("	SELECT		warehouse_detail.barcode as `barcode`,
										warehouse.client_object_id  as `location`
							FROM		a_letter
							LEFT JOIN	warehouse ON warehouse.a_letter_id = a_letter.id
							LEFT JOIN	warehouse_detail ON warehouse_detail.warehouse_id = warehouse.id
							WHERE		a_letter.id = '$letter_id'");

	while ( $aRow = mysql_fetch_array( $csql ) ){
		mysql_query("	UPDATE 	`reserve`
						SET 	`reserve`.`object_id` = '$aRow[location]'
						WHERE	`reserve`.`barcode` = '$aRow[barcode]' ");
	}
}

function MakeSale($user_id, $altter_id){
	$c_date = date('Y-m-d H:i:s');
	mysql_query("INSERT INTO sale_transaction
					(user_id, object_id, start_date, end_date, status)
				VALUES
					($user_id, 131, '$c_date', '$c_date', 11)");
	$transaction_id = mysql_insert_id();
	$sumcost		= 0.0;
	$rResult= mysql_query("	SELECT			`warehouse_producing`.`id` AS `warehouse_producing_id`,
											`warehouse_producing`.`production_id` as `production_id`,
											`cartridge_identity`.`amount` as `amount`,
											(SELECT ROUND(`reserve`.`price`, 2) FROM	reserve WHERE	`reserve`.`production_id` = `warehouse_producing`.`production_id` ORDER BY	reserve.id	DESC LIMIT 1) as `price`
							FROM			`warehouse`
							LEFT JOIN		`warehouse_detail` ON `warehouse_detail`.`warehouse_id` = `warehouse`.`id`
							LEFT JOIN		`warehouse_producing` ON `warehouse_producing`.`warehouse_detail_id` = `warehouse_detail`.`id`
							LEFT JOIN		`client_objects` ON `client_objects`.`id` = `warehouse`.`client_object_id`
							LEFT JOIN		`client` ON `client`.`id` = `client_objects`.`client_id`
							LEFT JOIN		`cartridge_identity` ON `cartridge_identity`.`production_id` = `warehouse_detail`.`prod_id` AND `cartridge_identity`.`parts_type` = `warehouse_producing`.`cartridge_part_id`
							WHERE  			`warehouse`.`a_letter_id` = '$altter_id'");

	while ( $aRow = mysql_fetch_array( $rResult ) )
	{
		mysql_query("	INSERT INTO sale_transaction_detail
							(user_id, transaction_id, warehouse_producing_id, production_id, product_quantity, self_cost, cost, cost_type)
						VALUES
							($user_id, $transaction_id, $aRow[warehouse_producing_id], $aRow[production_id], $aRow[amount], $aRow[price], $aRow[price], 1)");
		$sumcost = (float)$sumcost + (float)$aRow[price] * (float)$aRow[amount];
	}
	mysql_query("	UPDATE sale_transaction
					SET		sum_self_cost = $sumcost,
							sum_cost    	 = $sumcost
					WHERE id = '$transaction_id'");
}

function Savebletter($user_id, $altter_id, $requester, $new_requester, $client_obj,$responsible_person,$car_id){
	if(!empty($new_requester)){
			mysql_query("INSERT INTO `client_object_persons`
							(`user_id`, `client_object_id`, `name`)
						VALUES
							($user_id, $client_obj, '$new_requester')");

			$requester = mysql_insert_id();
	}
	$code='';
	if(!empty($requester)){
	    $code="`b_letter_status` = 1,
				b_letter_signature = '$requester',
	            b_letter_signature_date = NOW(),";

	}
	mysql_query("UPDATE
						`a_letter`
					SET
						$code
						receiver1         ='$responsible_person',
						car_id            = '$car_id'
					WHERE
						`id` = '$altter_id'");
}

function GetClientPersons($client_objects_id, $point){
	$data = '';
	$req = mysql_query("SELECT		`client_object_persons`.`id`,
									`client_object_persons`.`name`
						FROM		`client_objects`
						LEFT JOIN	`client_object_persons` ON `client_objects`.`id` = `client_object_persons`.`client_object_id`
						LEFT JOIN	`client` ON `client`.`id` = `client_objects`.`client_id`
						WHERE		`client_objects`.`id` = '$client_objects_id'");

		$data .= '<option value="0" selected="selected">&nbsp;</option>';


	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $point){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}
	return $data;
}

function addNull($sum){
	if($sum != '' && $sum != '0'){
		$parts = explode('.', $sum);
		$sum1 ='';
		$sum1.= $parts[0];
		if( $parts[1] == ''){
			$sum1.= '.00';
		}else{
			$sum1 = $sum;
		}
		return $sum1;
	}else{
		return '0.00';
	}
}
function barcode($id) {
   $rsql = mysql_query("SELECT 	warehouse_detail.id,
                        		warehouse_detail.barcode
                        FROM warehouse
                        JOIN warehouse_detail ON warehouse_detail.warehouse_id=warehouse.id
                        LEFT JOIN warehouse_producing ON warehouse_detail.id=warehouse_producing.warehouse_detail_id AND (warehouse_producing.cartridge_part_id=2 OR ISNULL(warehouse_producing.cartridge_part_id))
                        WHERE ISNULL(warehouse_producing.id) AND warehouse.a_letter_id=$id");
   $data = '<select id ="toneri">';
   $data.= '<option value="--">------</option>';
   while( $res = mysql_fetch_array($rsql)){

        {
           $data .= '<option value="' . $res[0] . '">' . $res[1] . '</option>';
        }
   }
   $data .= '</select>
       </th><th><button id="add_toner">ტონერის ჩამატება</button>';
   return  $data;

}

function print1($aletter_id){
	$rsql = mysql_query("SELECT    b_letter_details.barcode,
                    				production.`name`,	
                    				COUNT(DISTINCT part1.barcode) AS toneti,
                    				COUNT(DISTINCT part2.id) AS barabani,
                    				COUNT(DISTINCT part3.id) AS magnituri,
                    				COUNT(DISTINCT part4.id) as rezini,
                    				COUNT(DISTINCT part5.id) as gamwmendi,
                    				COUNT(DISTINCT part6.id) as dozirebis,
                    				COUNT(DISTINCT part7.id) AS chipi,
                    				COUNT(DISTINCT part8.id) AS korpusi,
                    				COUNT(DISTINCT part9.id) AS not_service,
                    				max(warehouse_detail.type),
                    				SUM(IF(warehouse_detail.type=2,0.00, b_letter_details.price))
                    FROM `b_letter_details`
                    JOIN  a_letter on b_letter_details.a_letter_id=a_letter.id
                    LEFT JOIN  production ON SUBSTR(b_letter_details.barcode, 6, 4) = production.id
                    LEFT JOIN  client ON SUBSTR(b_letter_details.barcode, 2, 4) = client.id
                    JOIN  warehouse_detail ON b_letter_details.warehouse_detail_id=warehouse_detail.id AND warehouse_detail.actived=1
                    LEFT JOIN  b_letter_details part1 ON  part1.part_type_id=2  AND part1.id =b_letter_details.id
                    LEFT JOIN  b_letter_details part2 ON  part2.part_type_id=3  AND part2.id =b_letter_details.id
                    LEFT JOIN  b_letter_details part3 ON  part3.part_type_id=4  AND part3.id =b_letter_details.id
                    LEFT JOIN  b_letter_details part4 ON  part4.part_type_id=5  AND part4.id =b_letter_details.id
                    LEFT JOIN  b_letter_details part5 ON  part5.part_type_id=6  AND part5.id =b_letter_details.id
                    LEFT JOIN  b_letter_details part6 ON  part6.part_type_id=7  AND part6.id =b_letter_details.id
                    LEFT JOIN  b_letter_details part7 ON  part7.part_type_id=8  AND part7.id =b_letter_details.id
                    LEFT JOIN  b_letter_details part8 ON  part8.part_type_id=9  AND part8.id =b_letter_details.id
                    LEFT JOIN  b_letter_details part9 ON  part9.part_type_id=16 AND part9.id =b_letter_details.id
                    WHERE      b_letter_details.a_letter_id=$aletter_id
                    GROUP BY b_letter_details.a_letter_id, b_letter_details.barcode"); 
	GLOBAL $sum;
		while ( $aRow = mysql_fetch_array( $rsql ) ){
			$data .= '<tr style="background-color: #D9D9D9;border:1px solid black !important;">';
			for ( $i = 0 ; $i < 13 ; $i++ ){
				if($i< 2 || $i > 10){
					if( $i == 11){
						if( $aRow[$i] == 2 ){
							$aRow[12] = '0.00';
							$data .= '<td style="border:1px solid black !important; vertical-align: middle !important; text-align: center;"><img src="media/images/0.gif"></td>';
						}else{
							$data .= '<td style="border:1px solid black !important; vertical-align: middle !important; text-align: center;">&nbsp;</td>';
						}
					}else{
						$data .='<td style="border:1px solid black !important; vertical-align: middle !important; text-align: center;">'.$aRow[$i].'</td>';
					}

				}else{
					if($aRow[$i] == 1){
						$data .= '<td style="border:1px solid black !important; vertical-align: middle !important; text-align: center;"><img src="media/images/0.gif"></td>';
					}else{
						$data .= '<td style="border:1px solid black !important; vertical-align: middle !important; text-align: center;">&nbsp;</td>';
					}
				}
				if($i == 12){
					$sum = floatval($sum) + floatval($aRow[$i]);
				}
			}
			$data .= '</tr>';
		}
	return $data;
}
function GetPersons($person_id)
{
	$data = '';
	$req = mysql_query("SELECT `id`, `name`
						FROM `persons`
						WHERE `persons`.`position` !=13 && `persons`.actived != 0");

	$data .= '<option value="' . 0 . '" selected="selected">' . '' . '</option>';

	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $person_id){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}
	return $data;
}

function GetAutoNumber($point)
{
	$data = '';
	$req = mysql_query("SELECT	`id`, `name`
						FROM	`object`
						WHERE	`type` = 3 && `object`.actived != 0"); // <<<<---------------------Type!!!
	$data .= '<option value="' . 0 . '" selected="selected">' . '' . '</option>';
	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $point){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}

	return $data;
}
function GetPage($res = ''){
	GLOBAL $sum;
	$reQ = mysql_fetch_assoc(mysql_query("SELECT		`a_letter`.`id` as `id`,
														`a_letter`.`fact_end_date` as `date`,
														CONCAT(`client`.`name`,'(',`client_objects`.`name`, ')') AS `client`,
														`client_objects`.`id` as `obj_id`,
	                                                     rs_wdsl_status.end_date,
														`a_letter`.`b_letter_signature` as `b_letter_signature`,
														`a_letter`.`b_letter_overhead_id` as `overhead_id`,
	                                                    `a_letter`.car_id,
	                                                     `a_letter`.receiver1,
														a_letter.`status`
										FROM			`a_letter`
										LEFT JOIN		`client_objects` ON `a_letter`.`client_object_id` = `client_objects`.`id`
										LEFT JOIN		`client` ON `client`.`id` = `client_objects`.`client_id`
	                                    LEFT JOIN      rs_wdsl_status ON rs_wdsl_status.id=a_letter.b_letter_overhead_id
										WHERE			`a_letter`.`id` = '$res[id]'"));

	$data = '
		<div id="dialog-form">
				<fieldset>
					<legend>ძირითადი ინფორმაცია</legend>
					<table width="80%" class="dialog-form-table" cellpadding="10px" >
						<tr align="center">
							<th colspan="2">
								<label >ბ ბარათი №<span style="border-bottom: 1px solid #000; padding: 0 30px; margin-left: 50px;">' . $reQ['id']. '</span></label>
							</th>
						</tr>
						<tr align="center">
							<th colspan="2">
								<label >დასრულების თარიღი<span style="border-bottom: 1px solid #000; padding: 0 30px; margin-left: 50px;">' . $reQ['date']. '</span></label>
							</th>
						</tr>
						<tr align="center">
							<th colspan="2">
								<label >კლიენტი<span style="border-bottom: 1px solid #000; padding: 0 30px; margin-left: 50px;">' . $reQ['client']. '</span></label>
							</th>
						</tr>
					</table>
				</fieldset>
				<div style="padding: 2px;"></div>

				<fieldset>
					<table style="width: 550px !important; border:1px solid black !important;" cellpadding="0" cellspacing="0">
						<tr style="background-color: #D9D9D9;border:1px solid black !important;">
							<td style="background-color: #D9D9D9; font-size: 100%  !important; border:1px solid black !important;">&nbsp;</td>
							<td style="background-color: #D9D9D9; font-size: 100%  !important; border:1px solid black !important;">&nbsp;</td>
							<td colspan="10"  style="background-color: #D9D9D9; font-size: 100%  !important; border:1px solid black !important; text-align: center;">ჩატარებული სამუშაოები</td>
							<td style="background-color: #D9D9D9; font-size: 100%  !important; border:1px solid black !important;">&nbsp;</td>
						</tr>
						<tr style="background-color: #D9D9D9;!important; border:1px solid black !important;">
							<td style="width: 170px;font-size: 9pt; border:1px solid black !important; vertical-align: middle !important; text-align: center;"><span>შტრიხკოდი</span></td>
							<td style="width: 110px;font-size: 9pt; border:1px solid black !important; vertical-align: middle !important; text-align: center;"><span>მოდელი</span></td>
							<td style="width: 25px !important; border:1px solid black !important;"><span style="writing-mode: tb-rl; -webkit-transform: rotate(270deg);	white-space: nowrap; display: block;width: 8px;	margin-top: 80px; margin-left: 4px;">ტონერი</span></td>
							<td style="width: 25px !important; border:1px solid black !important;"><span style="writing-mode: tb-rl; -webkit-transform: rotate(270deg);	white-space: nowrap; display: block;width: 8px;	margin-top: 80px; margin-left: 4px;">ბარაბანი</span></td>
							<td style="width: 25px !important; border:1px solid black !important;"><span style="writing-mode: tb-rl; -webkit-transform: rotate(270deg);	white-space: nowrap; display: block;width: 8px;	margin-top: 80px; margin-left: 4px;">მაგნიტ. ლილ.</span></td>
							<td style="width: 25px !important; border:1px solid black !important;"><span style="writing-mode: tb-rl; -webkit-transform: rotate(270deg);	white-space: nowrap; display: block;width: 8px;	margin-top: 80px; margin-left: 4px;">რეზინის PCR</span></td>
							<td style="width: 25px !important; border:1px solid black !important;"><span style="writing-mode: tb-rl; -webkit-transform: rotate(270deg);	white-space: nowrap; display: block;width: 8px;	margin-top: 80px; margin-left: 4px;">გამწ. რაკელი</span></td>
							<td style="width: 25px !important; border:1px solid black !important;"><span style="writing-mode: tb-rl; -webkit-transform: rotate(270deg);	white-space: nowrap; display: block;width: 8px;	margin-top: 80px; margin-left: 4px;">დოზ. რაკელი</span></td>
							<td style="width: 25px !important; border:1px solid black !important;"><span style="writing-mode: tb-rl; -webkit-transform: rotate(270deg);	white-space: nowrap; display: block;width: 8px;	margin-top: 80px; margin-left: 4px;">ჩიპი</span></td>
							<td style="width: 25px !important; border:1px solid black !important;"><span style="writing-mode: tb-rl; -webkit-transform: rotate(270deg);	white-space: nowrap; display: block;width: 8px;	margin-top: 80px; margin-left: 4px;">კორპუსი</span></td>
							<td style="width: 25px !important; border:1px solid black !important;"><span style="writing-mode: tb-rl; -webkit-transform: rotate(270deg);	white-space: nowrap; display: block;width: 8px;	margin-top: 80px; margin-left: 4px;">სერვისის გარეშე</span></td>
							<td style="width: 25px !important; border:1px solid black !important;"><span style="writing-mode: tb-rl; -webkit-transform: rotate(270deg);	white-space: nowrap; display: block;width: 8px;	margin-top: 80px; margin-left: 4px;">საგარანტიო</span></td>
							<td style="font-size: 100%  !important; width: 110px !important; vertical-align: middle !important; text-align: center;">&nbsp;ფასი&nbsp;</td>
						</tr>';

	$data .= print1($res['id']);
	$data .= '
    </table>
	                   <table >
						<div style="text-align: right; font-size: 10pt; margin-top: 4px;">
							<p>თანხა სულ :&nbsp;<span style="border-bottom: 1px solid #000; padding: 0 30px; margin-left: 0px;">' . addNull($sum) . '&nbsp;&nbsp;ლარი</span></p>
						</div>
					</table>
				</fieldset>
				<fieldset style="margin-top: 4px;">
				<table width="100%" class="dialog-form-table" cellpadding="10px" >
					<tr>
						<th></th>
						<th>'.barcode($res['id']).'</th>

					</tr>
                    <tr>
						<th></th>
						<th><label for="responsible_person">მძღოლი</label></th>
						<th><label for="car_id">ა/მ ნომერი</label></th>
					</tr>
					<tr>
						<th></th>
						<th>
							<select id="receiver1" class="idls address">'. GetPersons($reQ[receiver1])  .'</select>
						</th>
						<th>
							<select id="car_id" class="idls user_id">'. GetAutoNumber($reQ[car_id])  .'</select>
						</th>
					</tr>
						<tr>
							<th><label for="new_client_person"></label></th>
							<th><label for="requester">ხელმომწერელი პიროვნება</label></th>
							<th><label for="ok_part">&nbsp;</label></th>
						</tr>
						<tr>
							<td>
								<input type="checkbox" class="new_requester" class="idls" />
							</td>
							<td>
								<input id="new_requester" type="text" class="idle" onblur="this.className=\'idle\'" onfocus="this.className=\'activeField\'" style="display: none; width: 219px;" />
								<select id="requester" class="idls address">' . GetClientPersons($reQ['obj_id'], $reQ['b_letter_signature'] ) . '</select>
							</td>
							<td>
								<button type="button" id="ok_part" style="outline:none; margin-right: 140px;"class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-only">
									<span class="ui-button-text">შენახვა</span>
								</button>
							</td>
						</tr>
					</table>
				</fieldset>
	    </div>
	<div class="inner-table" >
		<input type="hidden" id="a_letter_id" value="' . $reQ[id] . '" />
		<input type="hidden" id="client_object_id" value="' . $reQ['obj_id'] . '" />
		<input type="hidden" id="overhead_id" value="' . $reQ['overhead_id'] . '" />
		<input type="hidden" id="client_ob_trans"  value="' . $res[transporter_company_id]. '" />     
		<input type="hidden" id="rs_end_date" value="' . $reQ['end_date'] . '" />

	</div>';

	return $data;
}

?>